/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.LoginDTO;
import dto.RoleDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBConfig;

/**
 *
 * @author vodangbinhtay
 */
public class LoginDAO {

    DBConfig con;
    Connection connect;
    ArrayList<LoginDTO> list = null;
    String SQL_CREATE = "INSERT INTO Logins VALUES(?,?)";
    String SQL_READALL = "SELECT * FROM logins";
    String SQL_READBYID = "SELECT * FROM Logins WHERE AccID = ?";
    String SQL_READBYUSERNAME = "SELECT * FROM Logins WHERE UserName = ?";
    String SQL_UPDATE = "UPDATE Logins SET UserName=?, Password=? WHERE AccID=?";
    String SQL_DELETE = "DELETE Logins WHERE AccID=?";

    public LoginDAO() {
        con = new DBConfig();
        list = new ArrayList<LoginDTO>();
    }

    public LoginDTO create(LoginDTO login) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_CREATE);
            pst.setString(1, login.getUser());
            pst.setString(2, login.getPass());
            pst.execute();
            return login;

        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public ArrayList<LoginDTO> realAll() {
        try {
            connect = con.OpenConnect();
            Statement st = connect.createStatement();
            ResultSet rs = st.executeQuery(SQL_READALL);
            if (rs != null) {
                while (rs.next()) {
                    LoginDTO login = new LoginDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
                    list.add(login);
                }
                return list;
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public LoginDTO readByID(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYID);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    LoginDTO login = new LoginDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
                    return login;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }
    
    public LoginDTO readByUserName(String uName) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst = connect.prepareStatement(SQL_READBYUSERNAME);
            pst.setString(1, uName);
            ResultSet rs = pst.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    LoginDTO login = new LoginDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
                    return login;
                }
            }
            return null;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public int update(LoginDTO login) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_UPDATE);
            pst.setString(1, login.getUser());
            pst.setString(2, login.getPass());
            pst.setInt(3, login.getAccID());
            return pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }

    }

    public int delete(int id) {
        try {
            connect = con.OpenConnect();
            PreparedStatement pst;
            pst = connect.prepareStatement(SQL_DELETE);
            pst.setInt(1, id);
            int r = pst.executeUpdate();
            return r;
        } catch (SQLException ex) {
            Logger.getLogger(LoginDAO.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        } finally {
            con.CloseConnect(connect);
        }
    }

    public static void main(String[] args) {
        LoginDAO dao = new LoginDAO();
        LoginDTO dto = new LoginDTO(2, "veve", "Hoahoa");
        LoginDTO l = dao.create(dto);
//        dto.setPass("1111");
//        dto.setUser("uuu");
//        int r = dao.update(dto);
//        LoginDTO l = dao.readByID(2);
//        l = dao.readByUserName("uuu");
        int rs = dao.delete(6);
        //dao.delete();
    }
}
